﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Drawing;
using System.Windows.Forms;
using System.Configuration;

namespace LibrarySystem
{
    public partial class AppManager
    {
        SqlConnection conn = new SqlConnection();
        string dbstr = "integrated security=SSPI;" + "data source='.';" + "initial catalog='alssafa'";

        public AppManager()
        {
            conn.ConnectionString = dbstr;
            conn.Open();
        }
        ~AppManager()
        {
            //conn.Close();
        }
        public void DBConnect(string str)
        {

        }
        public void GridDisplay(DataGridView dgv, string tablename, string commtxt)
        {
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            SqlDataAdapter dgvad = new SqlDataAdapter();
            SqlCommand dgvcomm = new SqlCommand();
            DataSet dgvds = new DataSet();
            dgvcomm.CommandType = CommandType.Text;
            dgvcomm.Connection = conn;
            dgv.ClearSelection();
            dgvds.Clear();
            dgvcomm.CommandText = commtxt;
            try
            {
                dgvad.SelectCommand = dgvcomm;
                dgvad.Fill(dgvds, tablename);
                dgv.DataSource = dgvds.Tables[tablename];
                conn.Close();
            }

            catch (SqlException)
            {
                MessageBox.Show("راجع البيانات المدخلة ");
            }
        }
        public void ComboDisplay(ComboBox cmb, string commtxt, string tablename, string membername)
        {
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            SqlDataAdapter cmbad = new SqlDataAdapter();
            SqlCommand cmbcomm = new SqlCommand();
            DataSet cmbds = new DataSet();
            cmbcomm.CommandType = CommandType.Text;
            cmbcomm.Connection = conn;
            cmbds.Clear();
            cmbcomm.CommandText = commtxt;
            cmbad.SelectCommand = cmbcomm;
            cmbad.Fill(cmbds, tablename);
            cmb.DataSource = cmbds.Tables[tablename];
            cmb.DisplayMember = membername;
            conn.Close();
        }

        public void ComboDisplayChoose(ComboBox cmb, string commtxt, string tablename, string membername,TextBox TEXT)
        {
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            SqlDataAdapter cmbad = new SqlDataAdapter();
            SqlCommand cmbcomm = new SqlCommand();
            DataSet cmbds = new DataSet();
            cmbcomm.CommandType = CommandType.Text;
            cmbcomm.Connection = conn;
            cmbds.Clear();
            cmbcomm.CommandText = commtxt;
            try
            {
                cmbad.SelectCommand = cmbcomm;
                cmbad.Fill(cmbds, tablename);
                cmb.DataSource = cmbds.Tables[tablename];
                cmb.ValueMember = membername;
                TEXT.Text = cmb.SelectedValue.ToString();
                conn.Close();
            }
            catch (SqlException)
            {
                // MessageBox.Show("راجع البيانات المدخلة ");

            }

            catch (NullReferenceException)
            {
                //MessageBox.Show("");
            }
        }

        public void ComboDisplayChoose2(ComboBox cmb, string commtxt, string tablename, string membername, ComboBox TEXT)
        {
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            SqlDataAdapter cmbad = new SqlDataAdapter();
            SqlCommand cmbcomm = new SqlCommand();
            DataSet cmbds = new DataSet();
            cmbcomm.CommandType = CommandType.Text;
            cmbcomm.Connection = conn;
            cmbds.Clear();
            cmbcomm.CommandText = commtxt;
            try
            {
                cmbad.SelectCommand = cmbcomm;
                cmbad.Fill(cmbds, tablename);
                cmb.DataSource = cmbds.Tables[tablename];
                cmb.ValueMember = membername;
                TEXT.Text = cmb.SelectedValue.ToString();
                conn.Close();
            }
            catch (SqlException)
            {
                // MessageBox.Show("راجع البيانات المدخلة ");
            }
        }

        public void ComboDisplayChoose3(ComboBox cmb, string commtxt, string tablename, string membername, Label TEXT)
        {
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            SqlDataAdapter cmbad = new SqlDataAdapter();
            SqlCommand cmbcomm = new SqlCommand();
            DataSet cmbds = new DataSet();
            cmbcomm.CommandType = CommandType.Text;
            cmbcomm.Connection = conn;
            cmbds.Clear();
            cmbcomm.CommandText = commtxt;
            try
            {
                cmbad.SelectCommand = cmbcomm;
                cmbad.Fill(cmbds, tablename);
                cmb.DataSource = cmbds.Tables[tablename];
                cmb.ValueMember = membername;
                TEXT.Text = cmb.SelectedValue.ToString();
                conn.Close();
            }
            catch (SqlException)
            {
                // MessageBox.Show("راجع البيانات المدخلة ");
            }
        }

        public void ComboDisplayBill(ComboBox cmb, string commtxt, string tablename, string membername, Label TEXT)
        {
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            SqlDataAdapter cmbad = new SqlDataAdapter();
            SqlCommand cmbcomm = new SqlCommand();
            DataSet cmbds = new DataSet();
            cmbcomm.CommandType = CommandType.Text;
            cmbcomm.Connection = conn;
            cmbds.Clear();
            cmbcomm.CommandText = commtxt;
            try
            {
                cmbad.SelectCommand = cmbcomm;
                cmbad.Fill(cmbds, tablename);
                cmb.DataSource = cmbds.Tables[tablename];
                cmb.ValueMember = membername;
                TEXT.Text = cmb.SelectedValue.ToString();
                conn.Close();
            }
            catch (SqlException)
            {
                // MessageBox.Show("راجع البيانات المدخلة ");
            }
            catch (NullReferenceException)
            {
                //MessageBox.Show("رقم الفاتورة غير موجود");
            }
        }


        public void FunSUM( string commtxt, Label TEXT)
        {
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            SqlDataAdapter cmbad = new SqlDataAdapter();
            SqlCommand cmbcomm = new SqlCommand();
            DataTable cmbds = new DataTable();
            cmbcomm.CommandType = CommandType.Text;
            cmbcomm.Connection = conn;
            cmbds.Clear();
            cmbcomm.CommandText = commtxt;
            try
            {
                cmbad.SelectCommand = cmbcomm;
                cmbad.Fill(cmbds);
                TEXT.Text = cmbds.Rows[0][0].ToString();
                conn.Close();
            }
            catch (SqlException)
            {
                // MessageBox.Show("راجع البيانات المدخلة ");
            }
        }

        
        public void insert(string inscommtxt)    
        {
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            SqlDataAdapter insad = new SqlDataAdapter();
            SqlCommand inscomm = new SqlCommand();
            DataSet insds = new DataSet();
            inscomm.CommandType = CommandType.Text;
            inscomm.Connection = conn;
            inscomm.CommandText = inscommtxt;
            try
            {
                inscomm.ExecuteNonQuery();
                MessageBox.Show("تمت عملية الإضافة");
            }
            catch (SqlException )
            {
               MessageBox.Show("راجع البيانات المدخلة ");
            }
        }

        public void insertBill(string inscommtxt)
        {
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            SqlDataAdapter insad = new SqlDataAdapter();
            SqlCommand inscomm = new SqlCommand();
            DataSet insds = new DataSet();
            inscomm.CommandType = CommandType.Text;
            inscomm.Connection = conn;
            inscomm.CommandText = inscommtxt;
            try
            {
                inscomm.ExecuteNonQuery();
                //MessageBox.Show("تمت عملية الإضافة");
            }
            catch (SqlException)
            {
                MessageBox.Show("راجع البيانات المدخلة ");
            }
        }
        public void delete(string delcommtxt)
        {
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            SqlCommand delcomm = new SqlCommand();
            delcomm.CommandType = CommandType.Text;
            delcomm.Connection = conn;
            delcomm.CommandText = delcommtxt;
            try
            {
                delcomm.ExecuteNonQuery();
              
            }
            catch (SqlException inserror)
            {
                MessageBox.Show(inserror.Message.ToString());
                
            }
        }
        public void update(string upcommtxt)
        {
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            SqlCommand upcomm = new SqlCommand();
            upcomm.CommandType = CommandType.Text;
            upcomm.Connection = conn;
            upcomm.CommandText = upcommtxt;
            try
            {
                upcomm.ExecuteNonQuery();
            }
            catch (SqlException inserror)
            {
                MessageBox.Show(inserror.Message.ToString());
            }
        }
    }
}
